﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;
using KPIS.GERP.GIMS.IDAL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class PrePurchaseRequestRouteHistoryDAL : IDALPrePurchaseRequestRouteHistory
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public void Insert(PrePurchaseRequestRouteHistoryInfo info)
        {

        }

        public void Insert(IList<PrePurchaseRequestRouteHistoryInfo> ListInfo, IDBManager dbManagerTrans)
        {
            string strSQL;
            try
            {
                DataSet ds;
                Nullable<int> lastSeq = null;
                for (int i = 0; i < ListInfo.Count; i++)
                {

                    dbManagerTrans.CreateParameters(11);

                    dbManagerTrans.AddParameters(0, "@revision_no", ListInfo[i].RevisionNo);
                    dbManagerTrans.AddParameters(1, "@icm_ppr_seq", ListInfo[i].InfoPrePurchaseRequest.Seq);
                    dbManagerTrans.AddParameters(2, "@from_emp_seq", ListInfo[i].InfoFromEmp.Seq);
                    dbManagerTrans.AddParameters(3, "@to_emp_seq", ListInfo[i].InfoToEmp.Seq);
                    dbManagerTrans.AddParameters(4, "@remark", ListInfo[i].Remark);
                    dbManagerTrans.AddParameters(5, "@order_seq", ListInfo[i].Order);
                    lastSeq = (ListInfo[i].Type == "APPROVE") ? lastSeq : null;
                    dbManagerTrans.AddParameters(6, "@upper_icm_ppr_route_his_seq", lastSeq);
                    dbManagerTrans.AddParameters(7, "@route_type", ListInfo[i].Type);
                    dbManagerTrans.AddParameters(8, "@route_status", ListInfo[i].Status);
                    dbManagerTrans.AddParameters(9, "@created_by", ListInfo[i].InfoRecord.Created_by);
                    dbManagerTrans.AddParameters(10, "@created_when", ListInfo[i].InfoRecord.Created_when);

                    strSQL = "INSERT INTO icm_pre_purchase_request_route_history(" +
                            "revision_no,icm_ppr_seq,from_emp_seq,to_emp_seq,remark,order_seq," +
                            "upper_icm_ppr_route_his_seq,route_type,route_status,created_by,created_when)" +
                            " values (" +
                            "@revision_no," +
                            "@icm_ppr_seq," +
                            "@from_emp_seq," +
                            "@to_emp_seq," +
                            "@remark," +
                            "@order_seq," +
                            "@upper_icm_ppr_route_his_seq," +
                            "@route_type," +
                            "@route_status," +
                            "@created_by," +
                            "@created_when)";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);

                    strSQL = "SELECT LAST_INSERT_ID()"
                        + " FROM icm_pre_purchase_request_route_history";
                    ds = dbManagerTrans.ExecuteDataSet(CommandType.Text, strSQL);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Update(PrePurchaseRequestRouteHistoryInfo info)
        {

        }

        public void Delete(PrePurchaseRequestRouteHistoryInfo info)
        {

        }

        public IList<PrePurchaseRequestRouteHistoryInfo> Select()
        {
            return null;
        }

        public int GetNextRevisionNo(IDBManager dbManagerTrans,int PprSeq)
        {
            try
            {
                dbManagerTrans.CreateParameters(1);
                dbManagerTrans.AddParameters(0,"@icm_ppr_seq", PprSeq);
                string strSql;
                strSql = "select coalesce((select coalesce(max( revision_no) + 1, 1)" +
                        " from icm_pre_purchase_request_route_history " +
                        " where icm_ppr_seq = @icm_ppr_seq " +
                        " group by icm_ppr_seq), 1)";
                DataSet ds;
                ds = dbManagerTrans.ExecuteDataSet(CommandType.Text, strSql);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    return Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
                }
                throw new Exception("");
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
